package org.anyline.simple.dml;


import org.anyline.adapter.DataWriter;
import org.anyline.data.adapter.DataWriterFactory;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.Compare;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.metadata.Column;
import org.anyline.metadata.Index;
import org.anyline.metadata.Table;
import org.anyline.metadata.type.TypeMetadata;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.simple.dml.entity.User;
import org.anyline.util.ConfigTable;
import org.anyline.util.LogUtil;
import org.anyline.util.SQLUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

import java.util.*;

@SpringBootApplication


public class DMLApplication {

	private static AnylineService service;
	private static String seq = null;
	private static Logger log = LoggerFactory.getLogger(DMLApplication.class);
	public static void main(String[] args) throws Exception{
		ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
		SpringApplication application = new SpringApplication(DMLApplication.class);

		ConfigurableApplicationContext context = application.run(args);

		service = ServiceProxy.service();

		check(null, "MySQL");
		check("pg", "PostgreSQL");
		check("ms", "SQL Server");
		//check("ms2000", "SQL Server 2000");
		check("oracle", "Oracle 11G");
		check("db2", "DB2");
		keys();
	}

	public static void keys(){
		HashSet<String> set = new HashSet<>();
		set.addAll(keys(null, "MySQL"));
		set.addAll(keys("pg", "PostgreSQL"));
		set.addAll(keys("ms", "SQL Server"));
		set.addAll(keys("oracle", "Oracle 11G"));
		System.out.println(set);
		keys("pg", "PostgreSQL");
		//keys("ms", "SQL Server");
		//keys("oracle", "Oracle 11G");

		//keys("ms2000", "SQL Server 2000");
		//keys("db2", "DB2");
	}
	public static List<String> keys(String ds, String title){
		service = ServiceProxy.service(ds);

		List<String> list = new ArrayList<>();
		ConfigTable.IS_LOG_SQL = false;
		for(String k: SQLUtil.keys){
			Table tab = null;
			try{
				tab = new Table("chk_key_"+ds+"_"+k);
				tab.addColumn(k,"INT");
				service.ddl().create(tab);
			}catch (Exception e){
				list.add(k);
				try {
					service.ddl().drop(tab);
				}catch (Exception xe){}
			}
		}
		System.out.println(title+":"+list);
		return list;
	}

	public static void check(String ds, String title) throws Exception{
		System.out.println("\n=============================== START " + title + "=========================================\n");
		if(null != ds) {
			service = ServiceProxy.service(ds);
		}

		seq = null;
		if("oracle".equals(ds)){
			seq = "SIMPLE_SEQ";
			if(null != service.query("USER_SEQUENCES","SEQUENCE_NAME:" + seq)) {
				service.execute("DROP SEQUENCE " + seq);
			}
			String sql = "CREATE SEQUENCE "+seq+" MINVALUE 0 START WITH 0 NOMAXVALUE INCREMENT BY 1 NOCYCLE CACHE 100";
 			service.execute(sql);
		}
		init();
		type();
		try {
			override();
		}catch (Exception e){
			e.printStackTrace();
		}
		try {
			columns();
		}catch (Exception e){
			e.printStackTrace();
		}
		try {
		//batch();
		}catch (Exception e){
			e.printStackTrace();
		}
		try {
			date();
		}catch (Exception e){
			e.printStackTrace();
		}
		try {
			insert();
		}catch (Exception e){
			e.printStackTrace();
		}
		query();
		page();
		delete();
		update();
		System.out.println("\n=============================== END " + title + "=========================================\n");
	}
	public static void type(){
		// 如果有项目中自定义的类型 在写入数据库时 驱动有可能不支持
		// 可以注册一个DataWriter，实现write方法，在写入数据库时如果匹配到相关的类型 会调用write方法执行类型转换
		// write要把Object value转换成java默认类型，这样系统内部会转换成驱动需要的类型
		// 或者直接转换成数据库驱动支持的类型

		DataWriterFactory.reg(new Object[]{java.util.Date.class, User.class, "其他class"},new DataWriter() {
			@Override
			public Object write(Object value, Boolean placeholder, Boolean unicode, TypeMetadata type) {
				Object result = value;
				if(result instanceof User){
					//遇到User类型 返回主键值
					result = ((User)result).getId();
				}
				//把value转换成 java默认类型 或 数据库驱动中提供的类型
				System.out.println(LogUtil.format("用户自定义类型转换器", 31));
				return result;
			}
		});
	}
	public static void update(){
		DataRow row = new DataRow();
		row.put("id",1);
		row.put("NAME","ABC");
		ConfigStore configs = new DefaultConfigStore();
		configs.and("USER_ID",1);
		service.update("crm_user", row, configs);
		List<Run> runs = configs.runs();
		for(Run run: runs){
			System.out.println(run.getFinalUpdate(false));
		}
	}
	public static void columns(){

		DataRow row = new DataRow();
		row.put("id",1);
		row.put("name","Z");
		row.put("code", null);

		//默认只插入不为空的列
		//INSERT INTO CRM_USER(ID,NAME) VALUES (?,?)
		service.insert("CRM_USER", row);
		service.truncate("CRM_USER");
		row.put("id",2);
		//-表示忽略 +表示必选  其他列正常处理
		//INSERT INTO CRM_USER(NAME,CODE) VALUES (?,?)
		service.insert("CRM_USER", row, "-id","+code");

		//只插入name  国为没有-+出现，所以不处理其他列
		//INSERT INTO CRM_USER(NAME) VALUES (?)
		service.insert("CRM_USER", row, "name");

		//INSERT INTO CRM_USER(NAME) VALUES (?)
		service.insert("CRM_USER", row, "-id");
	}
	public static void init(){
		try {
			Table table = service.metadata().table("CRM_USER");
			if (null != table) {
				service.ddl().drop(table);
			}
			table = new Table("CRM_USER");
			table.addColumn("ID", "INT").autoIncrement(true).primary(true);
			table.addColumn("CODE", "varchar(20)");
			table.addColumn("NAME", "varchar(20)");
			table.addColumn("USER_ID", "int");
			table.addColumn("AGE", "int");
			table.addColumn("YMD", "DATE");
			table.addColumn("YMD_HMS", "DATETIME");
			table.addColumn("HMS", "TIME");
			table.addColumn("L","LONG").setComment("long");
			table.addColumn("NM","varchar(50)").setComment("名称");
			table.addColumn("my","money").setComment("金额");
			table.addColumn("REG_TIME","DATETIME").setComment("日期");
			table.addColumn("CREATE_TIME","DATE").setComment("日期");
			table.addColumn("DEPARTMENT_ID","INT");
			table.addColumn("performance","decimal(10,2)");
			service.ddl().save(table);
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	public static void batch() throws Exception{

		List set = new ArrayList<>();
		List values = new ArrayList();
		for(int i=1; i<=500; i++){
			Map<String,String> row = new HashMap<>();
			//只插入NAME  ID自动生成 REG_TIME 默认当时时间
			row.put("NAME", "N"+i);
			row.put("ID", ""+i);
			set.add(row);
			values.add(i);
		}
		long qty = service.execute(50, "INSERT INTO CRM_USER(NAME) VALUES (?)", values);
		qty = service.insert(50, "CRM_USER", set, "NAME");
		qty = service.update(50, "CRM_USER", set,   "NAME");
		service.deletes(50, "CRM_USER", "ID", values);

	}
	public static void override() throws Exception{
		String tab = "tab_override";
		Table table = service.metadata().table(tab);
		if(null != table){
			service.ddl().drop(table);
		}
		table = new Table(tab);
		table.addColumn("ID", "BIGINT").autoIncrement(true);
		table.addColumn("CODE1", "varchar(10)");
		table.addColumn("CODE2", "varchar(10)");
		table.addColumn("CODE3", "varchar(10)");
		table.addColumn("QTY", "INT");

		Index index = new Index<>();
		index.setUnique(true);
		index.addColumn("CODE1");
		index.addColumn("CODE2");
		index.addColumn("CODE3");
		index.setTable("tab_override");
		table.add(index);
		service.ddl().create(table);
		DataRow row = new DataRow();
		row.put("CODE1", 1);
		row.put("CODE2", 2);
		row.put("CODE3", 3);
		row.put("QTY", 100);
		service.insert(tab, row);
		row = new DataRow();
		row.put("CODE1", 1);
		row.put("CODE2", 2);
		row.put("CODE3", 3);
		row.put("QTY", 200);
		//不覆盖，重复数据忽略 影响行数0
		service.insert(tab, row, new DefaultConfigStore().override(Boolean.FALSE, "CODE1","CODE2","CODE3"));
		DataSet set = service.querys(tab);
		System.out.println(set);


		row = new DataRow();
		row.put("CODE1", 1);
		row.put("CODE2", 2);
		row.put("CODE3", 3);
		row.put("QTY", 300);
		//覆盖,重复数据更新 影响行数2 原来的id:1,qty:100被删除 插入id:3, qty:300
		//注意这里MySQL执行的是delete+insert 而不是update 所以影响2行 PG执行update所以影响1行
		service.insert(tab, row, new DefaultConfigStore().override(Boolean.TRUE, "CODE1","CODE2","CODE3"));
		set = service.querys(tab);
		System.out.println(set);
	}
	//日期类型
	public static void date() throws Exception{
		//ConfigTable.IS_AUTO_CHECK_METADATA = true;
		DataRow row = new DataRow();
		row.put("YMD", new Date());
		row.put("YMD_HMS", new Date());
		row.put("HMS", new Date());
		if(null != seq){
			row.put("ID", "${"+seq+".NEXTVAL}");
		}
		service.insert("CRM_DATE", row);
	}
	public static void insert() throws Exception{

		DataRow r  = service.query("CRM_USER");
		//ConfigTable.IS_AUTO_CHECK_METADATA = true;
		System.out.println("\n-------------------------------- start insert  --------------------------------------------\n");


		ConfigTable.IS_AUTO_CHECK_METADATA = true;
		ConfigTable.IS_INSERT_EMPTY_COLUMN = true;
		ConfigTable.IS_UPDATE_EMPTY_COLUMN = true;
		DataRow row = new DataRow();
		row.put("REG_TIME", "2020-01-01");
		row.put("NM","ZH");
		row.put("age","");
		row.put("performance", 12.2d);
		row.put("my","10.1"); //money
		row.put("CREATE_TIME", new Date());
		row.put("DEPARTMENT_ID", 1);
		if(null != seq){
			row.put("ID", "${"+seq+".NEXTVAL}");
		}
		//仅适用于msyql系列
		//override=true : replace into
		//override=false: insert ignore into
		ServiceProxy.insert("CRM_USER", row, new DefaultConfigStore().override(Boolean.FALSE));

		row = service.query("CRM_USER");

		//执行insert后row如果数据库自动生成ID这时会row中会有ID值
		//在有主键值的情况下执行save最终会调用update
		ServiceProxy.save("CRM_USER", row);
		row.remove("ID");

		//如果没有主键值则执行insert
		service.save("CRM_USER", row);


		DataSet set = new DataSet();
		for(int i=0; i<3; i++){
			DataRow item = new DataRow();
			item.put("NM", "name_"+i);
			item.put("DEPARTMENT_ID", i%2);
			set.add(item);
		}

		//注意因为没有主键值，所有以下两行都可以执行insert
		//区别是save需要逐行执行,因为需要逐行检测主键值, insert会批量执行
		if(null != seq){
			set.put("ID", "${"+seq+".NEXTVAL}");
		}
		service.insert("CRM_USER", set);
		service.save("CRM_USER", set);

		System.out.println("\n-------------------------------- end insert  ----------------------------------------------\n");
	}

	public static void query() throws Exception{
		System.out.println("\n-------------------------------- start query  --------------------------------------------\n");
		//查询情况比较灵活请参考
		// web环境不需要new DefaultConfigStore 参考 anyline-simple-query中的controller
		//经常继承AnylineController 调用其中的里的condition()生成ConfigStore condition约定格式参考 http://doc.anyline.org/s?id=1059
		ConfigStore configs = new DefaultConfigStore();
		//查询总行数
		long qty = service.count("CRM_USER");

		//查询全部
		DataSet set = service.querys("CRM_USER");

		//按条件查询
		set = service.querys("CRM_USER", configs,"ID:1");

		//FIND_IN_SET
		//如果从request中取值  condition("[CODE]:CODE");condition("[CODE]:split(CODE)")

		configs.and("ID","9,0".split(","));
		configs.and("NM","a,b".split(","));
		configs.and(Compare.NOT_LIKE ,"NM", "ZH");
		//如果传入的值为空，则生成 WHERE CODE IS NULL
		configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "CODE" , "");
		//如果传入的值为空，按原样处理 会生成 NM=''或NM IS NULL
		configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "NM" ,null);
		Column ctype = service.metadata().column("CRM_USER","TYPES");
		if(null == ctype){
			ctype = new Column("TYPES", "varchar(100)").setTable("CRM_USER");
			service.ddl().add(ctype);
		}
		configs.and(Compare.FIND_IN_SET, "TYPES", "9");
		//传多个值时FIND_IN_SET默认与FIND_IN_SET_OR效果一样
		//configs.and(Compare.FIND_IN_SET, "TYPES", "A,B".split(","));
		//configs.and(Compare.FIND_IN_SET_OR, "TYPES", "1,2,3".split(","));
		//configs.and(Compare.FIND_IN_SET_AND, "TYPES", "1,2,3".split(","));
		//configs.or(Compare.FIND_IN_SET_OR, "TYPES", "4,5,6".split(","));

		//configs.or(Compare.FIND_IN_SET_AND, "TYPES", "4,5,6".split(","));
		//configs.ors(Compare.FIND_IN_SET_OR, "TYPES", "4,5,6".split(","));
		//configs.ors(Compare.FIND_IN_SET_AND, "TYPES", "4,5,6".split(","));
		//find_in_set 只在mysql中有实现 FIND_IN_SET(?,TYPES)
		service.querys("CRM_USER", configs, "ID:");

		System.out.println("\n-------------------------------- end query  ----------------------------------------------\n");
	}

	public static void page() throws Exception{
		System.out.println("\n-------------------------------- start page  --------------------------------------------\n");
		System.out.println("\n-------------------------------- end page  ----------------------------------------------\n");
	}
	public static void delete() throws Exception{
		System.out.println("\n-------------------------------- start delete  --------------------------------------------\n");
		System.out.println("\n-------------------------------- end delete  ----------------------------------------------\n");
	}

}
